*---------------------------------------------------------------------------------------------------------------;
* DESCRIPTION:		The program creates English value labels for admission categories and keep one permit per 
					person per date using the procedures described in the publication:
							"Opholdstilladelser p� individniveau" Danmarks Statistik, 21. november 2014
					(Google search: PSD_STATUS_OPHOLDSGRUND). 
					-> 4,8 percent of the observations in admission data (OPHG) are dropped using this procedure	
					OBS: Date of admission is set to 1st of january in the reference year if the admission 
					category is imputed 	
* PROGRAMMED BY:	Mette Foged																				   	;
* CREATED:			July 2017																			   		;
* LAST MODIFIED:	May 2022																		   			;
*---------------------------------------------------------------------------------------------------------------;


dm 'cle log';
dm 'cle out';

libname grund 'H:\Rawdata\707455\Grunddata';
libname migdata 'L:\Workdata\707455\Papers\ABFHP_1999reform\Code\ReStat\Input';

%let outputdata = L:\Workdata\707455\Papers\ABFHP_1999reform\Code\ReStat\Input;



* FORMATS;
proc format;
	value kat 	1	=	"Asylum"
				2	=	"Other"
				3	=	"Work"
				4	=	"EU/EEA"
				5	=	"Family"
				6	=	"Study"
				9997=	"Nordic citizen"
				9999=	"Immigrated before 1997";

	value gr	1	=	"Other"
				10	=	"Green Card (point-based)"
				11	=	"Job Card Scheme, from 1st of july 2008 Jobplanen"
				12	=	"Job change due to involuntary unemployment"
				13	=	"Company Permit Scheme" /*Koncernordningen*/
				135	=	"Selfemployed"
				136	=	"Special circumstances, minor children, Faroe Islands (paragraf 9 stk. 2 nr. 4)"
				137	=	"Establishment Card"
				138 =	"Association Agreement"
				139	=	"Drilling platform and the like"
				14	=	"Paid work, new EU countries"
				140	=	"Other family (EU-bkg. paragraf 13)"
				141	=	"Fast-track"
				142	=	"Researcherss (without PhD)"
				143	=	"Agriculture"
				144	=	"PhD student"
				145	=	"Start up Denmark"
				146	=	"Special individual qualifications"
				147	=	"Trainee"
				15 	=	"Paid work and independent profession"
				16	=	"Paid work and selfemployed"
				17	=	"The Positiv List"
				18	=	"Specialists"
				181	=	"Volunteer and Working Holiday"
				19 	=	"Other work"
				190	=	"Paretns older than 60, Faroe Islands (paragraf 9 stk. 1 nr. 4)"
				191	=	"Association-stand still"
				2	=	"Refugee"
				20	=	"Wage earner"
				21	=	"Student"
				22	=	"Other EU/EEA"
				23	=	"Special circumstances, minors (paragraf 9c stk. 1)"
				24	=	"Special circumstances, spouse or partner (paragraf 9c stk. 1)"
				25	=	"Special circumstances, spouse or partner (paragraf 9c stk. 1)"
				26	=	"Marriage or permanent relationship (paragraf 9 stk. 1 nr. 1)"
				27	=	"Marriage or permanent relationship (paragraf 9 stk. 1 nr. 1)"
				28	=	"Marriage or permanent relationship (paragraf 9 stk. 1 nr. 1)"
				29	=	"Minor children (paragraf 9 stk. 1 nr. 2)"
				3	=	"Adoption"
				30 	=	"Minor children"
				31	=	"Marriage or permanent relationship (paragraf 9 stk. 1 nr. 2)"
				32	=	"Marriage or permanent relationship (paragraf 9 stk. 1 nr. 2)"
				33	=	"Marriage or permanent relationship (paragraf 9 stk. 1 nr. 2)"
				34	=	"Minor children (paragraf 9 stk. 1 nr. 2)"
				35	=	"Minor children (paragraf 9 stk. 1 nr. 3)"
				36	=	"Minor children, Faroe Islands (paragraf 9 stk. 1 nr. 3)"
				37	=	"Minor children, Greenland (paragraf 9 stk. 1 nr. 3)"
				38	=	"Paretns older than 60 (paragraf 9 stk. 1 nr. 4)"
				39	=	"Special circumstances, minor children, Greenland (paragraf 9 stk. 2 nr. 4)"
				4	=	"Other"
				40	=	"Marriage or permanent relationship (paragraf 9 stk. 2 nr. 7)"
				41	=	"Child (EU bkg. paragraf 13)"
				42	=	"Family (EU citizen) to Danish citizen (EU bkg. paragraf 13)"
				43	=	"Family (third world) to Danish citizen (EU bkg. paragraf 13)"
				44	=	"Family to Danish citizen (EU bkg. paragraf 13)"
				45	=	"Parents (EU bkg. paragraf 13)"
				46	=	"Marriage or permanent relationship (EU bkg. paragraf 13)"
				47	=	"Refuge Network Scheme (Fribyordningen)"
				48	=	"Humanitarian work"
				49	=	"Intern"
				5 	= 	"Labor market"
				50	=	"Religious worker"
				51	=	"Study"
				52	=	"Other study"				
				53	=	"Parents older than 60 (paragraf 9, stk. 1, nr. 5)"
				6	=	"Au Pair"
				7	=	"Pay Limit Scheme"
				8	=	"Work and study at Greenland and the Faroe Islands"
				9	=	"Family of residence permit holder"
				9997=	"Nordic citizen"
				9999=	"Immigrated before 1997";

Value fkl		10 	= 	'Specialist on individual contract (paragraf 3 stk. 2)'
				100 = 	'Family relation to person with student permit'
				101 = 	'Herdsman'
				102 = 	'Researchers and teachers'
				103 = 	'Green Card (point-based)'
				104 = 	'Elementary- and secondary education'
				105 = 	'Athletes and trainers'
				106 = 	'Non-family relation to person, Faroe Islands and Greenland (EstherH)'
				107 = 	'Non-family relation to person in Denmark'
				108 = 	'No reference type'
				109 = 	'Engineers etc'
				11 	= 	'Student (paragraf 4 stk. 1)'
				110 = 	'IT workers'
				111 = 	'IT workers not on the positive list'
				112 = 	'Chef'
				113 = 	'Company Permit Scheme' 
				114 = 	'Course at a Folk High School'
				115 = 	'Agricultural worker'
				116 = 	'Manager (hovedgruppe 1 i DISCO 88)'
				117 = 	'Medical practitioner'
				118 = 	'Payroll assistant on the Faroe Islands'
				119 = 	'With hearing of besk�ftigelsesr�det'
				12 	= 	'EU-citizen on job deployment (paragraf 4 stk. 1)'
				120 = 	'Musicians and artists'
				121 = 	'Refuge Network Scheme (Fribyordningen)'
				122 = 	'Commuters'
				123 = 	'Interns'
				124 = 	'Reference Danish'
				125 = 	'Reference Danish/Nordic'
				126 = 	'Reference refugee'
				127 = 	'Reference nordic'
				129 = 	'Reference immigrant, not refugee'
				13 	= 	'Posted workers (paragraf 4 stk. 1-2)'
				130 = 	'Religious preachers'
				131 = 	'Social studies and economics'
				132 = 	'Entrepreneurs'
				133 = 	'Specialists'
				134 = 	'Specialists or managers without organizational hearing'
				135 = 	'Students on access granting courses'
				136 = 	'Nurses'
				137 = 	'Work permit for students from the new EU countries (Tidl. paragraf 13 stk. 3)'
				138 = 	'Previously Danish citizenship'
				139 = 	'Tivoli specialists'
				14 	= 	'Posted third world country citizens (paragraf 4 stk. 2)'
				140 = 	'Service personal in international organizations'
				141 = 	'Trainee'
				142 = 	'Without hearing of the arbejdsmarkedsr�d'
				143 = 	'Without hearing of the besk�ftigelsesr�d'
				144 = 	'Teaching, pedagogical work etc'
				145 = 	'Dismissal or contract termination'
				146 = 	'Knowledge worker in the field of technology and science'
				147 = 	'Higher education, non-Danish university'
				148 = 	'High education, Danish university'
				149 = 	'Volunteer, Danida-scholarship etc'
				15 	= 	'Student (paragraf 5 stk. 1)'
				150 = 	'Others on the Pay-Limit Scheme'
				151 = 	'Others on the Positive List'
				152 = 	'Other Payroll Employees'
				153 = 	'Other stays, Faroe Islands and Greenland(EstherH)'
				154 = 	'Other healthcare personalle'
				155 = 	'Dossier'
				156 = 	'Unaccompanied minor'
				157 = 	'Other reasons'
				159 = 	'Special individual qualification - Musicians and Artists'
				16 	= 	'Students (previously paragraf 4 stk 1, now paragraf 2 stk 1 and 10 stk 2)'
				160 = 	'Special individual qualifications - Specialised chefs'
				161 = 	'N/A'
				162 = 	'PhD-employed'
				163 = 	'Temporary Protection Status (paragraf 7 stk. 3)'
				164 = 	'Reference refugee, Convention/Protection Status'
				166 = 	'Agriculture - Operator'
				167 = 	'Unknown'
				168 = 	'Agriculture - Herdsman'
				169 = 	'Special individual qualifications - Athletes and trainers'
				17 	= 	'Sufficient funds (paragraf 5 stk. 1)'
				170 = 	'Cannot be expelled (paragraf 9c stk. 2)'
				171 = 	'Guest researcher'
				172 = 	'Family members'
				173 = 	'Unaccompanied minors'
				174 = 	'Establishment Card'
				175 = 	'Paragraf 9b stk 1'
				176 = 	'Employees'
				177 = 	'Family members of children seeking schooling'
				178 = 	'Fast-track researchers'
				179 = 	'Researchers (without PhD)'
				18 	= 	'Retirees on Social Insurance (paragraf 6 stk. 1)'
				180	=	'Job change due to involuntary unemployment'
				181	=	'Refugee, temporary status'
				182	=	'Specialised chefs'
				183 =	'Start Up Denmark'
				184	=	'Student'
				185 =	'Other with special qualifications'
				186	=	'Other on the Fast Track Scheme'
				187	=	'Refugee, Konvention/Protection Status'
				188 = 	'Volunteer and working holiday'
				189 = 	'Herdsman'
				19 	= 	'Sufficient Funds (paragraf 6 stk. 1)'
				190 = 	'Musicians and Artists'
				191	=	'Engineers'
				192	=	'Drilling platforms and the like'
				193	=	'Sufficient Funds (paragraf 6 stk. 1)'
				194	=	'Other study'
				197	=	'Other on Association-Stand Still'
				2	=	'Paid work (paragraf 1)'
				20 	= 	'Adequate funds (paragraf 6, stk. 1)'
				21	= 	'Retiree on Social Insurance (Folkepensionist paragraf 7 stk. 1)'
				22	= 	'Retiree on Social Insurance (Folkepensionist paragraf 7 stk. 1)'
				23	= 	'Convention status (paragraf 7 stk. 1)'
				24	= 	'B status (paragraf 7 stk. 2)'
				25	= 	'De facto status (paragraf 7 stk. 2)'
				26	= 	'De facto status (paragraf 7 stk. 4)'
				27	= 	'Convention status (paragraf 7 stk. 4)'
				28 	= 	'Quota (paragraf 8)'
				29	=	'Work permit (paragraf 9a stk. 1)'
				3	=	'Family, not EU/EEA citizen (paragraf 14)'
				30	=	'Work permit (paragraf 9a stk. 1)'
				31	=	'Wage earner and selfemployed (paragraf 9a stk. 1)'
				33	=	'The Positiv List / Special qualifications (paragraf 9a stk. 1)'
				34	=	'Job change due to involuntary unemployment (paragraf 9a stk. 17)'
				35	=	'After the end of a previous permit (paragraf 9a stk. 2)'
				36	=	'Green Card (point-based) (paragraf 9a stk. 2 nr. 1)'
				37	=	'The Positive List (paragraf 9a stk. 2 nr. 2)'
				38	=	'Pay Limit Scheme (paragraf 9a stk. 2 nr. 3)'
				39	=	'Company Permit Scheme (paragraf 9a stk. 2 nr. 4)'
				4 	= 	'Paid work (paragraf 3 stk. 1)'
				40	=	'Labour market (paragraf 9a stk. 2 nr. 5)'
				41 	= 	'Wage earner and selfemployed (paragraf 9a stk. 2 nr. 6)'
				42 	= 	'Green Card (paragraf 92, stk. 20)'
				43 	= 	'New EU case (paragraf 9a stk. 5)'
				44 	= 	'New EU case L65 (paragraf 9a stk. 5)'
				45 	= 	'New EU case L65 (paragraf 9a stk. 6)'
				46 	= 	'Humanitarian reasons (paragraf 9b stk. 1)'
				47 	= 	'Other (paragraf 9c stk. 1)'
				48 	= 	'Special circumstances (paragraf 9c stk. 1)'
				5 	= 	'Paid work (paragraf 3, stk. 1)'
				50 	=	'Special circumstances (paragraf 9c stk. 2)'	
				51 	= 	'Denied asylum, cannot be expelled (paragraf 9c stk. 2)'
				52 	= 	'Special circumstances (paragraf 9c stk. 3)'
				53 	= 	'Unaccompanied minors (paragraf 9c stk. 3)'
				54 	= 	'Unaccompanied minors (paragraf 9c stk. 3 nr. 1)'
				55 	= 	'Unaccompanied minors (paragraf 9c stk. 3 nr. 2)'
				56	= 	'Previously Danish citizen (paragraf 9d)'
				57	=	'Temporary permit (paragraf 9e stk. 1'
				58	=	'Religious preachers (paragraf 9f stk. 1)'
				59	=	'Previously Danish citizen (paragraf 9 stk. 1 nr. 1)'
				6	=	'Independent profession (paragraf 3 stk. 1)'
				60	=	'Adoption, foster care etc. (paragraf 9 stk. 1 nr. 3)'
				61	=	'Close relatives (paragraf 9 stk. 2 nr. 1)'
				62	=	'Humanitarian reasons (paragraf 9 stk. 2 nr. 2)'
				63	=	'Employment or work related reasons (paragraf 9 stk. 2 nr. 3)'
				64	=	'Special circumstances (paragraf 9 stk. 2 nr. 4)'
				65	=	'Temporary permit (paragraf 9 stk. 2 nr. 5)'
				66	=	'From Serbia-Montenegro denied under paragraf 7 (paragraf 9 stk. 2 nr. 6)'
				67	=	'Denied asylum, cannot be expelled (paragraf 9 stk. 2 nr. 4)'
				68	=	'Family who is EU citizen (paragraf 7-10)'
				69	=	'Family who is third country citizen (paragraf 7-10)'
				7	=	'Independent profession (paragraf 3 stk. 1)'
				70	=	'Family who is EU citizen (paragraf 8-12)'
				71	=	'Family who is third country citizen (paragraf 8-12)'
				72	=	'Family (paragraf 8-12)'
				73	=	'Adoption'
				74	=	'Adoption Faroe Islands'
				75	=	'Staff at embassies'
				76	=	'Other prior residence permit than refugee'
				77	=	'Other (paragraf 9.2.1)'
				78	=	'Other (paragraf 9.2.4)'
				79	=	'Other reasons than marriage and permenent relationship'
				8	=	'Wage earner at Faroe Islands (paragraf 3 stk. 1)'
				80	=	'Working Holiday'
				81	=	'Work permit for authorized medical practitioner'
				82	=	'Work permit to individuals with residence permit'
				83	=	'Work permit to students in higher education'
				84	=	'Association Agreement between EU/EEA and Tyrkey'
				85	=	'Au Pair'
				86	=	'Authorization as a medical practitioner'
				87	=	'Authorization as a dentist'
				88	=	'Staff at drilling platforms'
				89	=	'Children of other'
				9	=	'Employment of New EU citizens according to collective agreements (paragraf 3 stk. 2'
				90	=	'Children of refugees'
				91	=	'Circus specialist'
				92	=	'Danish descent'
				93	=	'Danish minority'
				94	=	'Danish minority in Germany'
				95	=	'Operator'
				96	=	'Work/study in Greenland'
				97	=	'Work/study at Faroe Islands'
				98	=	'Family to EU citizen working at Faroe Islands'
				99	=	'Family to person with work permit'
				9997 = 	'Nordic citizen'
				9998 = 	'Cannot be imputed'
				9999 = 	'Immigrated prior to 1997';
run;


*--------------------------------------------------------------------------------------------------------------
 	ADMISSION DATA
	1	Combine the annual admission registers (OPHG).
		Note: Date of approval is 1st of January ``1599'' for all imputed permits in the raw register. This
		data step changes the year of approval to the reference year such that one imputed permit will be kept  
		from each year (in step b) in the rare cases with more than one imputed permit per person.
---------------------------------------------------------------------------------------------------------------;
%let first=1997;
%let last=2019;

%macro m;
	%do t=&first. %to &last.;
	data ophg&t (drop = _:);
		set grund.ophg&t (where=(_pnr ne "") rename=(pnr=_pnr tilladelsesdato=_tilladelsesdato) 
						drop = version);
		pnr=_pnr+0;

		tilladelsesdato=_tilladelsesdato; format tilladelsesdato DDMMYY10.;
		if _tilladelsesdato='01jan1599'd then tilladelsesdato=intnx('year',_tilladelsesdato,&t.-1599);

		label 	tilladelsesdato="Date of approval"
				kategori	=	"Admission category level 1"
				grundlag	=	"Admission category level 2"
				forklar		=	"Admission category level 3"
				imputeret	=	"Imputed"
				pnr			=	"Person id";
	run;
	%end;
%mend m;
%m;

data ophg0;
	set ophg1997-ophg&last.;
run;


* CHECK DATA QUALITY: 2.6 PERCENT OF THE OBSERVATIONS ARE IDENTICAL TO OTHER OBSERVATIONS - DELETE;
proc sort data=ophg0 nodupkey; by pnr tilladelsesdato descending referencetid kategori grundlag forklar; run;


/* CHECK DATA QUALITY: NO ONE HAS AN IMPUTED PERMIT ON A DATE WHEN THEY ALSO HAVE A NON-IMPUTED PERMIT - HENCE 
WE CAN USE THE PROCEEDURE BELOW TO PRIORITIZE BETWEEN DIFFERENT PERMITS OBTAINED AT THE SAME DATE;
proc sort data=ophg0 nodupkey out=a; by pnr tilladelsesdato imputeret;run;
proc sort data=ophg0 nodupkey out=a; by pnr tilladelsesdato ;run;*/


*--------------------------------------------------------------------------------------------------------------
	2 	Keep one permit per person per date using Statistics Denmark's procedure
		->	4.8 percent of the observations in permit data are dropped
---------------------------------------------------------------------------------------------------------------;


data ophg1;
	set ophg0;
	by pnr tilladelsesdato descending referencetid kategori grundlag forklar; 
	tal+1;
	if first.tilladelsesdato then tal=1;
	format kategori kat. grundlag gr. forklar fkl.;
run;
proc freq data=ophg1; table tal; run;

data ophg2 (drop = tal);
	set ophg1 (where=(tal=1));
proc sort; by pnr imputeret tilladelsesdato; 
run;


*--------------------------------------------------------------------------------------------------------------
	3	COUNT THE NUMBER OF PERMITS THE INDIVIDUAL HAS OBTAINED OVER TIME INCLUDING IMPUTED PERMITS 
---------------------------------------------------------------------------------------------------------------;


data migdata.admissions;
	set ophg2;
	by pnr imputeret tilladelsesdato;

	count+1;
	if first.pnr then count=1;

	count_real+1-imputeret;
	if first.pnr then count_real=1-imputeret;
run;


*-------------------------------------------------------------------------------------------------------------;


* CLEAR;
proc datasets lib=work kill memtype=data;
title "";
run;
quit;


*--------------------------------------------------------------------------------------------------------------
	4	CHECKING THE DATA 
---------------------------------------------------------------------------------------------------------------;


data control1 (keep = pnr count: kategori rename=(kategori=kategori_last));
	set migdata.admissions;
	by pnr;
	if last.pnr;
	count_imputed=count-count_real;
run;

data control2 (keep = pnr kategori rename=(kategori=kategori_first));
	set migdata.admissions;
	by pnr;
	if first.pnr;
run;

data control3;
	merge control1 control2;
	by pnr;
run;



data control4;
	merge migdata.admissions control3 (keep = pnr kategori_first count:);
	by pnr;
	year=year(tilladelsesdato);
proc sort; by pnr year;
run;

data control5;
	set control4;
	by pnr year;
	count_withinyear+1;
	if first.year then count_withinyear=1;
run;
proc sort; by pnr count_withinyear; run;

data control6;
	set control5;
	by pnr;
	if last.pnr;
run;



proc sort data=control4 (where=(kategori_first=1)) nodupkey out=a; by pnr year imputeret;run;
proc sort data=control4 (where=(kategori_first=1)) nodupkey out=a; by pnr year ;run;




proc export data=migdata.admissions (drop = count_real) outfile="&outputdata.\Admissions_latest_record.dta" replace; run;


*-------------------------------------------------------------------------------------------------------------;


proc datasets lib=migdata;
delete admissions;
run;
quit;


* CLEAR ;
proc datasets lib=work kill memtype=data;
title "";
run;
quit;


*-------------------------------------------------------------------------------------------------------------;
